<!DOCTYPE html>
<html>
<head>
    <title>存储过程[未整理]</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./T-SQL[未整理].html" class="">第三章:T-SQL</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./事务-索引-视图[未整理].html" class="">事务-索引-视图[未整理]</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./子查询[未整理].html" class="">子查询[未整理]</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./存储过程[未整理].html" class="selected">存储过程[未整理]</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 存储过程[未整理] </h1>
            <div class="doc_post"> <h1 id="toc_0"><span class="md_align_center md_header" style="text-align:center; display: block;">第六章:存储过程</span></h1>

<h2 id="toc_1">什么是存储过程</h2>

<ul>
<li>  存储过程（procedure）类似于写EXCEL中的函数,JAVA中的方法</li>
<li>  用来执行管理任务或应用复杂的业务规则</li>
<li>  存储过程可以带参数，也可以返回结果</li>
<li>  存储过程可以包含数据操纵语句、变量、逻辑 控制语句等</li>
</ul>

<h2 id="toc_2">存储过程的优点</h2>

<ul>
<li>执行速度更快</li>
<li>允许模块化程序设计 </li>
<li>提高系统安全性</li>
<li>减少网络流通量</li>
</ul>

<h1 id="toc_3">存储过程的分类</h1>

<ol>
<li>系统存储过程

<ul>
<li>由系统定义，存放在master数据库中</li>
<li>类似C语言中的系统函数</li>
<li>系统存储过程的名称都以“sp_”开头或”xp_”开头</li>
</ul></li>
<li>用户自定义存储过程

<ul>
<li>由用户在自己的数据库中创建的存储过程</li>
<li>类似C语言中的用户自定义函数</li>
</ul></li>
</ol>

<h2 id="toc_4">常用的扩展存储过程：xp_cmdshell</h2>

<p class="md_block">
    <span class="md_line">可以执行DOS命令下的一些的操作 <br /></span>
    <span class="md_line">以文本行方式返回任何输出 <br /></span>
    <span class="md_line">调用语法：<br /></span>
    <span class="md_line">  EXEC xp_cmdshell DOS命令 [NO_OUTPUT]</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--开启高级配置               </span>
<span class="k">EXEC</span> <span class="n">sp_configure</span> <span class="s1">&#39;show advanced options&#39;</span><span class="p">,</span><span class="mi">1</span>  
<span class="k">GO</span>
<span class="c1">--重新配置(使上一条系统配置生效)</span>
<span class="n">RECONFIGURE</span>
<span class="k">GO</span>
<span class="c1">--开启允许xp_cmdshell的权限</span>
<span class="k">EXEC</span> <span class="n">sp_configure</span> <span class="s1">&#39;xp_cmdshell&#39;</span><span class="p">,</span><span class="mi">1</span>   
<span class="k">go</span> 
<span class="c1">--重新配置(使上一条系统配置生效) </span>
<span class="n">RECONFIGURE</span>
<span class="k">GO</span>
<span class="c1">--已经有执行系统DOS命令的权限了.....</span>
<span class="k">EXEC</span> <span class="n">xp_cmdshell</span> <span class="s1">&#39;&#39;</span>    
<span class="c1">--做完之后记得关闭,出于安全考滤</span>
<span class="k">EXEC</span> <span class="n">sp_configure</span> <span class="s1">&#39;xp_cmdshell&#39;</span><span class="p">,</span><span class="mi">0</span>  
<span class="k">GO</span>
<span class="n">RECONFIGURE</span>
<span class="k">GO</span>
<span class="k">EXEC</span> <span class="n">sp_configure</span> <span class="s1">&#39;show advanced options&#39;</span><span class="p">,</span><span class="mi">0</span> 
<span class="k">GO</span>
<span class="n">RECONFIGURE</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">常用存储过程</span>
</p>

<table>
 <thead><tr><th style="text-align:left">存储过程</th>
<th style="text-align:left">功能说明</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">sp_databases </td>
<td style="text-align:left"> 列出服务器上的所有数据库。</td>
</tr>
<tr><td style="text-align:left">sp_helpdb </td>
<td style="text-align:left"> 报告有关指定数据库或所有数据库的信息</td>
</tr>
<tr><td style="text-align:left">sp_renamedb </td>
<td style="text-align:left"> 更改数据库的名称</td>
</tr>
<tr><td style="text-align:left">sp_tables </td>
<td style="text-align:left"> 返回当前环境下可查询的对象的列表</td>
</tr>
<tr><td style="text-align:left">sp_columns   </td>
<td style="text-align:left"> 回某个表列的信息</td>
</tr>
<tr><td style="text-align:left">sp_help </td>
<td style="text-align:left"> 查看某个表的所有信息</td>
</tr>
<tr><td style="text-align:left">sp_helpconstraint </td>
<td style="text-align:left"> 查看某个表的约束</td>
</tr>
<tr><td style="text-align:left">sp_helpindex </td>
<td style="text-align:left"> 查看某个表的索引</td>
</tr>
<tr><td style="text-align:left">sp_stored_procedures </td>
<td style="text-align:left"> 列出当前环境中的所有存储过程。</td>
</tr>
<tr><td style="text-align:left">sp_password </td>
<td style="text-align:left"> 添加或修改登录帐户的密码。</td>
</tr>
<tr><td style="text-align:left">sp_helptext </td>
<td style="text-align:left"> 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。</td>
</tr>
</tbody> 
</table>
<!--block_code_end--><div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">EXEC</span> <span class="n">sp_databases</span>
<span class="k">EXEC</span>  <span class="n">sp_renamedb</span> <span class="s1">&#39;旧数据库名&#39;</span><span class="p">,</span><span class="s1">&#39;新数据库名&#39;</span>
<span class="n">USE</span> <span class="n">stuDB</span>
<span class="k">GO</span>
<span class="k">EXEC</span> <span class="n">sp_tables</span>
<span class="k">EXEC</span> <span class="n">sp_columns</span> <span class="err">表名</span>
<span class="k">EXEC</span> <span class="n">sp_help</span> <span class="err">表名</span>
<span class="k">EXEC</span> <span class="n">sp_helpconstraint</span> <span class="err">表名</span>
<span class="k">EXEC</span> <span class="n">sp_helpindex</span> <span class="err">表名</span>
<span class="k">EXEC</span> <span class="n">sp_helptext</span> <span class="s1">&#39;视图名&#39;</span> 
<span class="k">EXEC</span> <span class="n">sp_stored_procedures</span>  
</pre></div>

<!--block_code_end-->
<h2 id="toc_5">创建存储过程的基本语法</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">create</span> <span class="n">proc</span> <span class="err">存储过程名</span>
<span class="o">@</span><span class="err">参数</span><span class="mi">1</span> <span class="err">类型</span>
<span class="p">,</span><span class="o">@</span><span class="err">参数</span><span class="mi">2</span><span class="err">类型</span> <span class="k">OUTPUT</span>
<span class="p">,</span><span class="o">@</span><span class="err">参数</span><span class="mi">3</span><span class="err">类型</span> <span class="o">=</span> <span class="err">默认值</span>
<span class="k">AS</span>
    <span class="c1">--功能的实现</span>
<span class="k">GO</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_6">1.简单存储过程 无参数</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="n">PROC</span> <span class="n">PR_KP1</span>
<span class="k">AS</span>
    <span class="n">PRINT</span> <span class="s1">&#39;这是一个简单无参存储过程&#39;</span>
<span class="k">GO</span>
<span class="c1">--调用</span>
<span class="k">EXEC</span> <span class="n">PR_KP1</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_7">2.带普通参数的存储过程</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="n">PROC</span> <span class="n">PR_KP2</span>
<span class="o">@</span><span class="n">param</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span>
<span class="k">AS</span>
    <span class="n">PRINT</span> <span class="o">@</span><span class="n">param</span>    
<span class="k">GO</span>
<span class="k">EXEC</span> <span class="n">PR_KP2</span> <span class="s1">&#39;这是参数@param的值&#39;</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_8">3.带默认值的参数(可选参数)</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="n">PROC</span> <span class="n">PR_KP3</span>
<span class="o">@</span><span class="n">param</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="o">=</span> <span class="s1">&#39;这是我的默认值.&#39;</span>
<span class="k">AS</span>
    <span class="n">PRINT</span> <span class="o">@</span><span class="n">param</span>    
<span class="k">GO</span>
<span class="k">EXEC</span> <span class="n">PR_KP3</span> <span class="s1">&#39;这是参数@param的值&#39;</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_9">4.普通能数与默认值的共用,默认值的必须放最后.</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="n">PROC</span> <span class="n">PR_KP4</span>
<span class="o">@</span><span class="n">param0</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span>
<span class="p">,</span><span class="o">@</span><span class="n">param</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="o">=</span> <span class="s1">&#39;这是我的默认值.&#39;</span>
<span class="k">AS</span>
    <span class="n">PRINT</span> <span class="o">@</span><span class="n">param</span>    
<span class="k">GO</span>
<span class="k">EXEC</span> <span class="n">PR_KP4</span> <span class="s1">&#39;这是参数@param的值&#39;</span>
<span class="c1">--实例2:如果有多个可选参数,则如下</span>
<span class="k">alter</span> <span class="n">PROC</span> <span class="n">PR_KP4</span>
<span class="o">@</span><span class="n">param0</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span>
<span class="p">,</span><span class="o">@</span><span class="n">param</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="o">=</span> <span class="s1">&#39;这是我的默认值11111.&#39;</span>
<span class="p">,</span><span class="o">@</span><span class="n">param2</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="o">=</span> <span class="s1">&#39;这是我的默认值22222.&#39;</span>
<span class="k">AS</span>
    <span class="n">PRINT</span> <span class="o">@</span><span class="n">param0</span> 
    <span class="n">PRINT</span> <span class="o">@</span><span class="n">param</span> 
    <span class="n">PRINT</span> <span class="o">@</span><span class="n">param2</span>  
<span class="k">GO</span>
<span class="k">EXEC</span> <span class="n">PR_KP4</span> <span class="s1">&#39;这是参数@param的值&#39;</span><span class="p">,</span><span class="k">default</span><span class="p">,</span><span class="s1">&#39;aaaaaaa&#39;</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_10">5.带输出参数的存储过程</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="n">PROC</span> <span class="n">PR_KP5</span>
<span class="o">@</span><span class="n">param</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">200</span><span class="p">)</span> <span class="k">OUTPUT</span>
<span class="k">AS</span>
    <span class="k">SET</span> <span class="o">@</span><span class="n">param</span> <span class="o">=</span> <span class="s1">&#39;这是输出参数的值!&#39;</span>
<span class="k">GO</span>
<span class="c1">--调用</span>
<span class="k">DECLARE</span> <span class="o">@</span><span class="n">temp</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">200</span><span class="p">)</span>
<span class="k">EXEC</span> <span class="n">PR_KP5</span> <span class="o">@</span><span class="n">temp</span> <span class="k">OUTPUT</span>
<span class="n">PRINT</span> <span class="o">@</span><span class="n">temp</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_11">6.带RETURN(return后不跟变量名或值,则它是跳出存储过程,如果跟值或变量,则返回值并跳出存储过程),如果用它返回值,只能返回int类型,一般放在条件中或存储过程最后.</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">ALTER</span> <span class="n">PROC</span> <span class="n">PR_KP6</span>
<span class="k">AS</span>
    <span class="k">RETURN</span> <span class="mi">123</span>
<span class="k">GO</span>
<span class="c1">--调用</span>
<span class="k">declare</span> <span class="o">@</span><span class="n">temp</span> <span class="nb">int</span>
<span class="k">EXEC</span> <span class="o">@</span><span class="n">temp</span> <span class="o">=</span> <span class="n">PR_KP6</span>
<span class="n">print</span> <span class="k">convert</span><span class="p">(</span><span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span><span class="o">@</span><span class="n">temp</span><span class="p">)</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_12">作业</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--1.写一个存储过程,创建一个数据库,要求:</span>
    <span class="c1">--参数1 数据库名</span>
    <span class="c1">--参数2 路径 (带默认值)</span>
    <span class="c1">--在创建前判断这个库是否存在,存在则删作</span>
    <span class="k">alter</span> <span class="n">PROC</span> <span class="n">PR_</span><span class="err">创建数据库</span>
<span class="o">@</span><span class="n">dbname</span> <span class="nb">varchar</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="c1">--数据库名</span>
<span class="p">,</span><span class="o">@</span><span class="n">path</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">200</span><span class="p">)</span> <span class="o">=</span> <span class="s1">&#39;E:\数据库勿删&#39;</span> <span class="c1">--数据库存放的路径</span>
<span class="k">AS</span>

<span class="k">exec</span><span class="p">(</span><span class="s1">&#39;IF EXISTS(SELECT NAME FROM sys.sysdatabases WHERE NAME=&#39;&#39;&#39;</span><span class="o">+@</span><span class="n">dbname</span><span class="o">+</span><span class="s1">&#39;&#39;&#39;)</span>
<span class="s1">    DROP DATABASE [&#39;</span><span class="o">+@</span><span class="n">dbname</span><span class="o">+</span><span class="s1">&#39;]&#39;</span><span class="p">)</span>
<span class="c1">-----------------------------------------</span>
<span class="c1">--在创建数据库之前要创建目录:学员自己完成</span>
<span class="c1">--开启高级配置               </span>
<span class="k">EXEC</span> <span class="n">sp_configure</span> <span class="s1">&#39;show advanced options&#39;</span><span class="p">,</span><span class="mi">1</span>  
<span class="c1">--重新配置(使上一条系统配置生效)</span>
<span class="n">RECONFIGURE</span>
<span class="c1">--开启允许xp_cmdshell的权限</span>
<span class="k">EXEC</span> <span class="n">sp_configure</span> <span class="s1">&#39;xp_cmdshell&#39;</span><span class="p">,</span><span class="mi">1</span>   
<span class="c1">--重新配置(使上一条系统配置生效) </span>
<span class="n">RECONFIGURE</span>
<span class="c1">--已经有执行系统DOS命令的权限了.....</span>


<span class="k">DECLARE</span> <span class="o">@</span><span class="n">temp</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">200</span><span class="p">)</span>
<span class="k">SET</span> <span class="o">@</span><span class="n">temp</span> <span class="o">=</span> <span class="s1">&#39;md &#39;</span><span class="o">+@</span><span class="n">path</span>
<span class="k">EXEC</span> <span class="n">xp_cmdshell</span> <span class="o">@</span><span class="n">temp</span><span class="p">,</span><span class="s1">&#39;NO_OUTPUT&#39;</span>

<span class="c1">--做完之后记得关闭,出于安全考滤</span>
<span class="k">EXEC</span> <span class="n">sp_configure</span> <span class="s1">&#39;xp_cmdshell&#39;</span><span class="p">,</span><span class="mi">0</span>  
<span class="n">RECONFIGURE</span>
<span class="k">EXEC</span> <span class="n">sp_configure</span> <span class="s1">&#39;show advanced options&#39;</span><span class="p">,</span><span class="mi">0</span> 
<span class="n">RECONFIGURE</span>
<span class="c1">-----------------------------------------</span>
<span class="c1">--解决路径中\问题(读取路径最后一个字符,看它是不是\)</span>
<span class="k">if</span><span class="p">(</span><span class="k">RIGHT</span><span class="p">(</span><span class="o">@</span><span class="n">path</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span> <span class="o">&lt;&gt;</span> <span class="s1">&#39;\&#39;</span><span class="p">)</span>
    <span class="k">set</span> <span class="o">@</span><span class="n">path</span> <span class="o">=</span> <span class="o">@</span><span class="n">path</span><span class="o">+</span><span class="s1">&#39;\&#39;</span>

<span class="k">EXEC</span><span class="p">(</span><span class="s1">&#39;CREATE DATABASE &#39;</span><span class="o">+@</span><span class="n">dbname</span><span class="o">+</span><span class="s1">&#39;</span>
<span class="s1">ON</span>
<span class="s1">(</span>
<span class="s1">    NAME = &#39;</span><span class="o">+@</span><span class="n">dbname</span><span class="o">+</span><span class="s1">&#39;_data</span>
<span class="s1">    ,FILENAME = [&#39;</span><span class="o">+@</span><span class="n">path</span><span class="o">+@</span><span class="n">dbname</span><span class="o">+</span><span class="s1">&#39;_data.mdf]</span>
<span class="s1">    ,FILEGROWTH = 10%</span>
<span class="s1">    ,SIZE = 5MB</span>
<span class="s1">)</span>
<span class="s1">LOG ON</span>
<span class="s1">(</span>
<span class="s1">    NAME = &#39;</span><span class="o">+@</span><span class="n">dbname</span><span class="o">+</span><span class="s1">&#39;_log</span>
<span class="s1">    ,FILENAME = [&#39;</span><span class="o">+@</span><span class="n">path</span><span class="o">+@</span><span class="n">dbname</span><span class="o">+</span><span class="s1">&#39;_log.ldf]</span>
<span class="s1">    ,FILEGROWTH = 10%</span>
<span class="s1">    ,SIZE = 5MB</span>
<span class="s1">    ,MAXSIZE = 50MB</span>
<span class="s1">)&#39;</span><span class="p">)</span>
<span class="n">PRINT</span> <span class="s1">&#39;创建成功&#39;</span>
<span class="k">GO</span>
<span class="c1">--调用</span>
<span class="k">EXEC</span> <span class="n">PR_</span><span class="err">创建数据库</span> <span class="s1">&#39;五班测试数据库BBB&#39;</span><span class="p">,</span><span class="s1">&#39;D:\测试路径1&#39;</span>
<span class="k">EXEC</span> <span class="n">PR_</span><span class="err">创建数据库</span> <span class="s1">&#39;五班测试数据库BBB&#39;</span>
    
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">加强练习作业2:</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--写一个存储过程完成转帐(由于学习难度,暂时取消,只写了部分代码,转帐与错误处理未写完)</span>
<span class="c1">--1.三个输入参数,转出人ID,转入人ID,钱,@输出参数 显示成功或不失败 </span>
<span class="c1">--2.要求有事务,转帐一旦失败则回滚,并给输出参数赋值&#39;失败&#39;,</span>
<span class="c1">--  成功能给输出参数赋值&#39;成功&#39; 并提交事务</span>
<span class="k">ALTER</span> <span class="n">PROC</span> <span class="err">转帐</span>
<span class="o">@</span><span class="n">flag</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="k">OUTPUT</span> <span class="c1">--用于输出成功还是失败</span>
<span class="p">,</span><span class="o">@</span><span class="n">outusrid</span> <span class="nb">INT</span>  <span class="c1">--转出人ID</span>
<span class="p">,</span><span class="o">@</span><span class="n">inusrid</span> <span class="nb">INT</span>   <span class="c1">--转入人ID</span>
<span class="p">,</span><span class="o">@</span><span class="n">money</span> <span class="n">MONEY</span>   <span class="c1">--钱</span>
<span class="k">AS</span>
<span class="c1">--1.开始事务</span>
<span class="k">BEGIN</span> <span class="n">TRAN</span>

<span class="c1">--2.转帐(思考可分几步?</span>
    <span class="c1">--1.判断两用户ID是否存在,</span>
<span class="k">DECLARE</span> <span class="o">@</span><span class="n">tempid</span> <span class="nb">INT</span>
<span class="k">SELECT</span> <span class="o">@</span><span class="n">tempid</span> <span class="o">=</span> <span class="k">COUNT</span><span class="p">(</span><span class="n">id</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">st_money</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="o">@</span><span class="n">outusrid</span>
<span class="k">IF</span><span class="p">(</span><span class="o">@</span><span class="n">tempid</span> <span class="o">&lt;&gt;</span> <span class="mi">1</span><span class="p">)</span> 
<span class="k">BEGIN</span>
    <span class="k">set</span> <span class="o">@</span><span class="n">flag</span> <span class="o">=</span> <span class="s1">&#39;转帐失败:转出用户不存在!&#39;</span>
    <span class="k">ROLLBACK</span>
    <span class="k">RETURN</span>
<span class="k">END</span>
<span class="k">DECLARE</span> <span class="o">@</span><span class="n">tempid1</span> <span class="nb">INT</span>
<span class="k">SELECT</span> <span class="o">@</span><span class="n">tempid1</span> <span class="o">=</span> <span class="k">COUNT</span><span class="p">(</span><span class="n">id</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">st_money</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="o">@</span><span class="n">inusrid</span>
<span class="k">IF</span><span class="p">(</span><span class="o">@</span><span class="n">tempid1</span> <span class="o">&lt;&gt;</span> <span class="mi">1</span><span class="p">)</span> 
<span class="k">BEGIN</span>
    <span class="k">set</span> <span class="o">@</span><span class="n">flag</span> <span class="o">=</span> <span class="s1">&#39;转帐失败:转入用户不存在!&#39;</span>
    <span class="k">ROLLBACK</span>
    <span class="k">RETURN</span>
<span class="k">END</span>
    <span class="c1">--2.判断转出帐户余额是否充足</span>
<span class="k">DECLARE</span> <span class="o">@</span><span class="n">tempmoney</span> <span class="n">MONEY</span>
<span class="k">SELECT</span> <span class="o">@</span><span class="n">tempmoney</span> <span class="o">=</span> <span class="n">MONEY</span> <span class="k">FROM</span> <span class="n">st_money</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="o">@</span><span class="n">outusrid</span>
<span class="k">IF</span><span class="p">(</span><span class="o">@</span><span class="n">tempmoney</span> <span class="o">&lt;</span> <span class="o">@</span><span class="n">money</span><span class="p">)</span>
<span class="k">BEGIN</span>
    <span class="k">set</span> <span class="o">@</span><span class="n">flag</span> <span class="o">=</span> <span class="s1">&#39;转帐失败:转出人余额不足!&#39;</span>
    <span class="k">ROLLBACK</span>
    <span class="k">RETURN</span>
<span class="k">END</span>
    <span class="c1">--3.防止参数@money为负数或0,转0无意义</span>
<span class="k">IF</span><span class="p">(</span><span class="o">@</span><span class="n">money</span> <span class="o">&lt;=</span> <span class="mi">0</span><span class="p">)</span>
<span class="k">BEGIN</span>
    <span class="k">set</span> <span class="o">@</span><span class="n">flag</span> <span class="o">=</span> <span class="s1">&#39;转帐失败:你想干什么???转帐金额必须大于0&#39;</span>
    <span class="k">ROLLBACK</span>
    <span class="k">RETURN</span>
<span class="k">END</span>
<span class="c1">--开始转帐....由于学生接收能力关系,暂停了</span>
    
<span class="c1">--最后要对语句异常进行处理.暂停了未写)</span>
<span class="c1">--3.要判断是否成功成功则提交事务给输出变量赋值,不成功则回滚事务并给输出变量赋值</span>
<span class="k">COMMIT</span> <span class="n">TRAN</span>
<span class="k">set</span> <span class="o">@</span><span class="n">flag</span> <span class="o">=</span> <span class="s1">&#39;转帐成功!&#39;</span>
<span class="k">GO</span>
<span class="c1">--调用代码</span>
<span class="k">DECLARE</span> <span class="o">@</span><span class="n">flag</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span>
<span class="k">EXEC</span> <span class="err">转帐</span> <span class="o">@</span><span class="n">flag</span> <span class="k">output</span><span class="p">,</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">100</span>
<span class="k">select</span> <span class="o">@</span><span class="n">flag</span>
</pre></div>

<!--block_code_end--> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>